﻿
Imports System.Data.SqlClient

Public Class NhaThueDAO

    
    Public Shared Function GetAllNhaDAO() As DataTable
        ' Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "DanhSach_NhaThue"
        cmd.Connection = cn
        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)
        Return dt


    End Function
    Public Shared Function GetAllNhaDAO_DaHuy() As DataTable

        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "DanhSach_NhaThue_DaHuy"
        cmd.Connection = cn
        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)
        Return dt


    End Function
    Public Shared Function GetNha_KhachHangDAO() As DataTable
        ' Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "DanhSach_NhaThue_KhachHang"
        cmd.Connection = cn
        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)
        Return dt


    End Function
    Public Shared Function GetMaNhaDAO() As DataTable
        'Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "DanhSachMa_NhaThue"
        cmd.Connection = cn
        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)
        Return dt


    End Function

    Public Shared Sub InsertNhaDAO(ByVal Nha As NhaThueDTO)
        ' Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "Insert_NhaThue"
        cmd.Connection = cn
        cmd.Connection.Open()

        '@MoTa nvarchar(50), @MaLoaiNha int, @DienTich float,
        '@Gia float,@CanOGhep char(1), @NguoiLienHe nvarchar(50), @DiaChi nvarchar(50),
        '@SoDienThoai varchar(11),@Email varchar(50), @NgayTiepNhan date,
        '@MaTinhTrang int,@MaCongTy int, @NguoiDangTin varchar(50)

        Dim para As SqlParameter = New SqlParameter("MoTa", SqlDbType.NVarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.MoTa
        cmd.Parameters.Add(para)

        para = New SqlParameter("MaLoaiNha", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.MaLoaiNha
        cmd.Parameters.Add(para)

        para = New SqlParameter("DienTich", SqlDbType.Float)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.DienTich
        cmd.Parameters.Add(para)

        para = New SqlParameter("Gia", SqlDbType.Float)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.Gia
        cmd.Parameters.Add(para)
     
        para = New SqlParameter("CanOGhep", SqlDbType.Char, 1)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.CanOGhep
        cmd.Parameters.Add(para)


        para = New SqlParameter("NguoiLienHe", SqlDbType.NVarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.NguoiLienHe
        cmd.Parameters.Add(para)

        para = New SqlParameter("DiaChi", SqlDbType.NVarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.DiaChi
        cmd.Parameters.Add(para)

        para = New SqlParameter("SoDienThoai", SqlDbType.VarChar, 11)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.SoDienThoai
        cmd.Parameters.Add(para)

  
        para = New SqlParameter("Email", SqlDbType.VarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.Email
        cmd.Parameters.Add(para)

        para = New SqlParameter("NgayTiepNhan", SqlDbType.Date)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.NgayTiepNhan
        cmd.Parameters.Add(para)

        para = New SqlParameter("MaTinhTrang", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.MaTinhTrang
        cmd.Parameters.Add(para)

        para = New SqlParameter("MaCongTy", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.MaCongTy
        cmd.Parameters.Add(para)

        para = New SqlParameter("NguoiDangTin", SqlDbType.VarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.NguoiDangTin
        cmd.Parameters.Add(para)

        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

    End Sub

    Public Shared Sub UpdateNhaDAO(ByVal Nha As NhaThueDTO)
        ' Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "Update_NhaThue"
        cmd.Connection = cn
        cmd.Connection.Open()

        Dim para As SqlParameter = New SqlParameter("MaNha", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.MaNha
        cmd.Parameters.Add(para)

        para = New SqlParameter("MoTa", SqlDbType.NVarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.MoTa
        cmd.Parameters.Add(para)

        para = New SqlParameter("MaLoaiNha", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.MaLoaiNha
        cmd.Parameters.Add(para)

        para = New SqlParameter("DienTich", SqlDbType.Float)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.DienTich
        cmd.Parameters.Add(para)

        para = New SqlParameter("Gia", SqlDbType.Float)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.Gia
        cmd.Parameters.Add(para)

        para = New SqlParameter("CanOGhep", SqlDbType.Char, 1)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.CanOGhep
        cmd.Parameters.Add(para)


        para = New SqlParameter("NguoiLienHe", SqlDbType.NVarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.NguoiLienHe
        cmd.Parameters.Add(para)

        para = New SqlParameter("DiaChi", SqlDbType.NVarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.DiaChi
        cmd.Parameters.Add(para)

        para = New SqlParameter("SoDienThoai", SqlDbType.VarChar, 11)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.SoDienThoai
        cmd.Parameters.Add(para)


        para = New SqlParameter("Email", SqlDbType.VarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.Email
        cmd.Parameters.Add(para)

        para = New SqlParameter("NgayTiepNhan", SqlDbType.Date)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.NgayTiepNhan
        cmd.Parameters.Add(para)

        para = New SqlParameter("MaTinhTrang", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.MaTinhTrang
        cmd.Parameters.Add(para)

        para = New SqlParameter("MaCongTy", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.MaCongTy
        cmd.Parameters.Add(para)

        para = New SqlParameter("NguoiDangTin", SqlDbType.VarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = Nha.NguoiDangTin
        cmd.Parameters.Add(para)


        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

    End Sub


    Public Shared Sub DeleteNhaDAO(ByVal MaNha As Integer)
        ' Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "Delete_NhaThue"
        cmd.Connection = cn
        cmd.Connection.Open()

        Dim para As SqlParameter = New SqlParameter("MaNha", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = MaNha
        cmd.Parameters.Add(para)
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

    End Sub


    Public Shared Sub DeleteNhaDAO_DaHuy(ByVal MaNha As Integer)
        ' Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "Delete_NhaThue_DaHuy"
        cmd.Connection = cn
        cmd.Connection.Open()

        Dim para As SqlParameter = New SqlParameter("MaNha", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = MaNha
        cmd.Parameters.Add(para)

        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

    End Sub

    Public Shared Sub RestoreNhaDAO_DaHuy(ByVal MaNha As Integer)
        ' Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "Restore_NhaThue_DaHuy"
        cmd.Connection = cn
        cmd.Connection.Open()

        Dim para As SqlParameter = New SqlParameter("MaNha", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = MaNha
        cmd.Parameters.Add(para)

        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

    End Sub

    Public Shared Sub Delete_CapNhat_TinhTrang_NhaDAO(ByVal MaNha As Integer)
        'Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "Delete_CapNhat_TinhTrang_NhaThue"
        cmd.Connection = cn
        cmd.Connection.Open()

        Dim para As SqlParameter = New SqlParameter("MaNha", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = MaNha
        cmd.Parameters.Add(para)
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

    End Sub

    '@TenCongTy nvarchar(50), @MaLoaiNha int,@TenQuan nvarchar(50), @Loai int
    'TimKiem_NhaThue
    Public Shared Function TimKiem_NhaThueDAO(ByVal TenCongty As String, ByVal MaLoaiNha As Integer, ByVal TenQuan As String, ByVal Loai As Integer) As DataTable
        'Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "TimKiem_NhaThue"


        Dim para As SqlParameter = New SqlParameter("TenCongTy", SqlDbType.NVarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = TenCongty
        cmd.Parameters.Add(para)

        para = New SqlParameter("MaLoaiNha", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = MaLoaiNha
        cmd.Parameters.Add(para)

        para = New SqlParameter("TenQuan", SqlDbType.NVarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = TenQuan
        cmd.Parameters.Add(para)

        para = New SqlParameter("Loai", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = Loai
        cmd.Parameters.Add(para)

        cmd.Connection = cn
        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)
        Return dt


    End Function
    '@MaLoaiNha int ,@TenQuan nvarchar(50), @Loai int
    Public Shared Function TimKiem_NhaThue_KhachHangDAO(ByVal MaLoaiNha As Integer, ByVal TenQuan As String, ByVal Loai As Integer) As DataTable
        ' Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "TimKiem_NhaThue_KhachHang"



        Dim para As SqlParameter = New SqlParameter("MaLoaiNha", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = MaLoaiNha
        cmd.Parameters.Add(para)

        para = New SqlParameter("TenQuan", SqlDbType.NVarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = TenQuan
        cmd.Parameters.Add(para)

        para = New SqlParameter("Loai", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = Loai
        cmd.Parameters.Add(para)

        cmd.Connection = cn
        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)
        Return dt


    End Function

End Class
